<?php

class Articles {

    // các thuộc tính
    private $_ID;
    private $_title;
    private $_alias;
    private $_createdDate;
    private $_modifiedDate;
    private $_description;
    private $_content;
    private $_image;
    private $_imageStatus;
    private $_status;
    private $_order;
    private $_hits;
    private $_categoryID;
    private $_highLight;
    private $_createdUserID;
    private $_modifiedUserID;
    private $_limit;
    private $_startRow;
    private $_linePerPage;
    // các đối tượng
    private $_category;
    private $_createdUser;
    private $_modifiedUser;

    public function getStartRow() {
        return $this->_startRow;
    }

    public function getLinePerPage() {
        return $this->_linePerPage;
    }

    public function setStartRow($startRow) {
        $this->_startRow = $startRow;
    }

    public function setLinePerPage($linePerPage) {
        $this->_linePerPage = $linePerPage;
    }

    public function getLimit() {
        return $this->_limit;
    }

    public function setLimit($Limit) {
        $this->_limit = $Limit;
    }

    public function getID() {
        return $this->_ID;
    }

    public function setID($ID) {
        $this->_ID = $ID;
    }

    public function getTitle() {
        return $this->_title;
    }

    public function setTitle($title) {
        $this->_title = $title;
    }

    public function getAlias() {
        return $this->_alias;
    }

    public function setAlias($alias) {
        $this->_alias = $alias;
    }

    public function getCreatedDate() {
        return $this->_createdDate;
    }

    public function setCreatedDate($createdDate) {
        $this->_createdDate = $createdDate;
    }

    public function getModifiedDate() {
        return $this->_modifiedDate;
    }

    public function setModifiedDate($modifiedDate) {
        $this->_modifiedDate = $modifiedDate;
    }

    public function getDescription() {
        return $this->_description;
    }

    public function setDescription($description) {
        $this->_description = $description;
    }

    public function getContent() {
        return $this->_content;
    }

    public function setContent($content) {
        $this->_content = $content;
    }

    public function getImage() {
        return $this->_image;
    }

    public function setImage($image) {
        $this->_image = $image;
    }

    public function getImageStatus() {
        return $this->_imageStatus;
    }

    public function setImageStatus($imageStatus) {
        $this->_imageStatus = $imageStatus;
    }

    public function getStatus() {
        return $this->_status;
    }

    public function setStatus($status) {
        $this->_status = $status;
    }

    public function getOrder() {
        return $this->_order;
    }

    public function setOrder($order) {
        $this->_order = $order;
    }

    public function getHits() {
        return $this->_hits;
    }

    public function setHits($hits) {
        $this->_hits = $hits;
    }

    public function getCategoryID() {
        return $this->_categoryID;
    }

    public function setCategoryID($categoryID) {
        $this->_categoryID = $categoryID;
    }

    public function getHighLight() {
        return $this->_highLight;
    }

    public function setHighLight($highLight) {
        $this->_highLight = $highLight;
    }

    public function getCreatedUserID() {
        return $this->_createdUserID;
    }

    public function setCreatedUserID($createdUserID) {
        $this->_createdUserID = $createdUserID;
    }

    public function getModifiedUserID() {
        return $this->_modifiedUserID;
    }

    public function setModifiedUserID($modifiedUserID) {
        $this->_modifiedUserID = $modifiedUserID;
    }

    public function getCategory() {
        return $this->_category;
    }

    public function setCategory($category) {
        $this->_category = $category;
    }

    public function getCreatedUser() {
        return $this->_createdUser;
    }

    public function setCreatedUser($createdUser) {
        $this->_createdUser = $createdUser;
    }

    public function getModifiedUser() {
        return $this->_modifiedUser;
    }

    public function setModifiedUser($modifiedUser) {
        $this->_modifiedUser = $modifiedUser;
    }

    public function getHighLightList($db = null) {
        if ($db != null) {
            $sqlQuery = "SELECT A_ID, 
                                A_Title,
                                A_Alias, 
                                A_CreatedDate,
                                A_Description, 
                                A_Content,
                                A_Image, 
                                A_ImageStatus,
                                A_ModifiedDate, 
                                A_Status,
                                A_Order, 
                                A_Hits,
                                A_CategoryID, 
                                A_HighLight,
                                A_CreatedUserID, 
                                A_ModifiedUserID
                        FROM articles
                        WHERE A_HighLight = 1
                        ORDER BY A_Order DESC";
            $parameters = null;
            $values = null;
            $types = null;

            $result = $db->executeQuery($sqlQuery, $parameters, $values, $types);


            if ($result != null) {
                $rows = array();
                while ($row = $result->fetch()) {

                    $article = new Articles();
                    $article->setID($row->A_ID);
                    $article->setTitle($row->A_Title);
                    $article->setAlias($row->A_Alias);
                    $article->setCreatedDate($row->A_CreatedDate);
                    $article->setDescription($row->A_Description);
                    $article->setContent($row->A_Content);
                    $article->setImage($row->A_Image);
                    $article->setImageStatus($row->A_ImageStatus);
                    $article->setModifiedDate($row->A_ModifiedDate);
                    $article->setStatus($row->A_Status);
                    $article->setOrder($row->A_Order);
                    $article->setHits($row->A_Hits);
                    $article->setCategoryID($row->A_CategoryID);
                    $article->setHighLight($row->A_HighLight);

                    $article->setCreatedUserID($row->A_CreatedUserID);
                    $article->setModifiedUserID($row->A_ModifiedUserID);


                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($article->getCreatedUserID());
                    $createdUser->getByID($db);
                    $article->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($article->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $article->setModifiedUser($modifiedUser);

                    $rows[] = $article;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    // tải danh sách bài viết theo mã chủ đề
    public function getListByCategoryID($db) {
        if ($db != null) {
            $sql = "SELECT
                          `A_ID`,
                          `A_Title`,
                          `A_Alias`,
                          Date_Format(`A_CreatedDate`, '%d/%m/%Y') AS `A_CreatedDate`, 
                          `A_Description`,
                          `A_Content`,
                          `A_Image`,
                          `A_ImageStatus`,
                          Date_Format(`A_ModifiedDate`, '%d/%m/%Y') AS `A_ModifiedDate`,
                          `A_Status`,
                          `A_Order`,
                          `A_Hits`,
                          `A_CategoryID`,
                          `A_HighLight`,
                          `A_CreatedUserID`,
                          `A_ModifiedUserID`
                        FROM `articles`
                        WHERE A_CategoryID IN ( 
                        SELECT  DISTINCT level1.C_ID        
        		FROM categories AS level1         
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID         
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID        
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID        
        		WHERE level1.C_ID = :A_CategoryID AND level1.C_ID <>-1    
                    
        		UNION
        
        		SELECT  DISTINCT level2.C_ID        
        		FROM categories AS level1         
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID         
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID        
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID        
        		WHERE level1.C_ID = :A_CategoryID AND level2.C_ID <>-1
        
        		UNION 
        
        		SELECT DISTINCT level3.C_ID        
        		FROM categories AS level1         
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID         
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID        
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID        
        		WHERE level1.C_ID = :A_CategoryID AND level3.C_ID <>-1
        
        		UNION 
        
        		SELECT DISTINCT level4.C_ID        
        		FROM categories AS level1         
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID         
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID     
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID        
        		WHERE level1.C_ID = :A_CategoryID AND level4.C_ID <>-1
        
        		UNION 
                        
        		SELECT DISTINCT level5.C_ID	        
        		FROM categories AS level1         
        		LEFT OUTER JOIN categories AS level2        
        		ON level2.C_Parent = level1.C_ID         
        		LEFT OUTER JOIN categories AS level3        
        		ON level3.C_Parent = level2.C_ID        
        		LEFT OUTER JOIN categories AS level4        
        		ON level4.C_Parent = level3.C_ID        
        		LEFT OUTER JOIN categories AS level5        
        		ON level5.C_Parent = level4.C_ID        
        		WHERE level1.C_ID = :A_CategoryID AND level5.C_ID <>-1  
                    )
                    ORDER BY A_Order DESC";

            $parameters = array(':A_CategoryID');
            $values = array($this->getCategoryID());
            $types = array(PDO::PARAM_INT);
            
//            echo $this->getCategoryID();
            
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $article = new Articles();
                    $article->setID($row->A_ID);
                    $article->setTitle($row->A_Title);
                    $article->setAlias($row->A_Alias);
                    $article->setCreatedDate($row->A_CreatedDate);
                    $article->setDescription($row->A_Description);
                    $article->setContent($row->A_Content);
                    $article->setImage($row->A_Image);
                    $article->setImageStatus($row->A_ImageStatus);
                    $article->setModifiedDate($row->A_ModifiedDate);
                    $article->setStatus($row->A_Status);
                    $article->setOrder($row->A_Order);
                    $article->setHits($row->A_Hits);
                    $article->setCategoryID($row->A_CategoryID);
                    $article->setHighLight($row->A_HighLight);

                    $article->setCreatedUserID($row->A_CreatedUserID);
                    $article->setModifiedUserID($row->A_ModifiedUserID);


                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($article->getCreatedUserID());
                    $createdUser->getByID($db);
                    $article->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($article->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $article->setModifiedUser($modifiedUser);


                    // category 
                    $category = new Categories();
                    $category->setID($article->getCategoryID());
                    $category = $category->getByID($db);
                    $article->setCategory($category);


                    $rows[] = $article;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    // tải danh sách bài viết theo mã chủ đề
    public function getList($db) {
        if ($db != null) {
            $sql = "SELECT
                          `A_ID`,
                          `A_Title`,
                          `A_Alias`,
                          Date_Format(`A_CreatedDate`, '%d/%m/%Y') AS `A_CreatedDate`,                          
                          `A_Description`,
                          `A_Content`,
                          `A_Image`,
                          `A_ImageStatus`,
                          Date_Format(`A_ModifiedDate`, '%d/%m/%Y') AS `A_ModifiedDate`,                          
                          `A_Status`,
                          `A_Order`,
                          `A_Hits`,
                          `A_CategoryID`,
                          `A_HighLight`,
                          `A_CreatedUserID`,
                          `A_ModifiedUserID`
                        FROM `articles`                       
                    ORDER BY A_Order DESC";

            $parameters = null;
            $values = null;
            $types = null;
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $article = new Articles();
                    $article->setID($row->A_ID);
                    $article->setTitle($row->A_Title);
                    $article->setAlias($row->A_Alias);
                    $article->setCreatedDate($row->A_CreatedDate);
                    $article->setDescription($row->A_Description);
                    $article->setContent($row->A_Content);
                    $article->setImage($row->A_Image);
                    $article->setImageStatus($row->A_ImageStatus);
                    $article->setModifiedDate($row->A_ModifiedDate);
                    $article->setStatus($row->A_Status);
                    $article->setOrder($row->A_Order);
                    $article->setHits($row->A_Hits);
                    $article->setCategoryID($row->A_CategoryID);
                    $article->setHighLight($row->A_HighLight);

                    $article->setCreatedUserID($row->A_CreatedUserID);
                    $article->setModifiedUserID($row->A_ModifiedUserID);


                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($article->getCreatedUserID());
                    $createdUser->getByID($db);
                    $article->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($article->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $article->setModifiedUser($modifiedUser);





                    $rows[] = $article;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }






    public function getAriticleByIDCategory($db) {
        if ($db != null) {
            $sql = "SELECT *
                    FROM `articles` WHERE  `articles`.`A_CategoryID`=:A_CategoryID                      
                    ORDER BY A_Order DESC
                    limit 4";

        $parameters = array(":A_CategoryID");
    $values = array($this->getCategoryID());
    $types = array(PDO::PARAM_INT);//kiem tra lai kieu du lieu nhe
    $rows = $db->executeQuery($sql, $parameters, $values, $types);
    if ($rows != null) {
     while ($row = $rows->fetch()) {
         $obj = new Articles();
                    $obj->setID($row->A_ID);
                    $obj->setTitle($row->A_Title);
                    $obj->setAlias($row->A_Alias);
                    $obj->setCreatedDate($row->A_CreatedDate);
                    $obj->setDescription($row->A_Description);
                    $obj->setContent($row->A_Content);
                    $obj->setImage($row->A_Image);
                    $objects[] = $obj;
            }
        return $objects;
        }
      }
    }










    public function getNewList($db) {
        if ($db != null) {
            $sql = "SELECT
                          `A_ID`,
                          `A_Title`,
                          `A_Alias`,
                          `A_CreatedDate`,                          
                          `A_Description`,
                          `A_Content`,
                          `A_Image`,
                          `A_ImageStatus`,
                          Date_Format(`A_ModifiedDate`, '%d/%m/%Y') AS `A_ModifiedDate`,                          
                          `A_Status`,
                          `A_Order`,
                          `A_Hits`,
                          `A_CategoryID`,
                          `A_HighLight`,
                          `A_CreatedUserID`,
                          `A_ModifiedUserID`
                        FROM `articles`                       
                    ORDER BY A_CreatedDate DESC";

            $parameters = null;
            $values = null;
            $types = null;
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

               

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $article = new Articles();
                    $article->setID($row->A_ID);
                    $article->setTitle($row->A_Title);
                    $article->setAlias($row->A_Alias);
                    $article->setCreatedDate($row->A_CreatedDate);
                    $article->setDescription($row->A_Description);
                    $article->setContent($row->A_Content);
                    $article->setImage($row->A_Image);
                    $article->setImageStatus($row->A_ImageStatus);
                    $article->setModifiedDate($row->A_ModifiedDate);
                    $article->setStatus($row->A_Status);
                    $article->setOrder($row->A_Order);
                    $article->setHits($row->A_Hits);
                    $article->setCategoryID($row->A_CategoryID);
                    $article->setHighLight($row->A_HighLight);

                    $article->setCreatedUserID($row->A_CreatedUserID);
                    $article->setModifiedUserID($row->A_ModifiedUserID);


                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($article->getCreatedUserID());
                    $createdUser->getByID($db);
                    $article->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($article->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $article->setModifiedUser($modifiedUser);


                    $rows[] = $article;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    // hàm cập nhật giá trị trạng thái
    public function updateStatus($db) {
        if ($db != null) {
            $sql = "UPDATE `articles`
                    SET `A_Status` = :A_Status
                    WHERE `A_ID` = :A_ID";
            $parameters = array(':A_Status', ':A_ID');
            $values = array($this->getStatus(), $this->getID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // hàm cập nhật giá trị highlight
    public function updateHighLight($db) {
        if ($db != null) {
            $sql = "UPDATE `articles`
                    SET `A_HighLight` = :A_HighLight
                    WHERE `A_ID` = :A_ID;";
            $parameters = array(':A_HighLight', ':A_ID');
            $values = array($this->getHighLight(), $this->getID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // update hits by articleID
    public function increaseHitsByID($db) {
        if ($db != null) {
            $sql = "UPDATE `articles`
                    SET `A_Hits` = A_Hits + 1
                    WHERE `A_ID` = :A_ID;";
            $parameters = array(':A_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // add a new article
    public function add($db) {
        if ($db != null) {
            $sql = "INSERT INTO `articles`
                                (
                                 `A_Title`,`A_Alias`,
                                 `A_CreatedDate`,`A_Description`,
                                 `A_Content`,`A_Image`,
                                 `A_ImageStatus`,`A_ModifiedDate`,
                                 `A_Status`,`A_Order`,
                                 `A_Hits`,`A_CategoryID`,
                                 `A_HighLight`,`A_CreatedUserID`,
                                 `A_ModifiedUserID`
                                 )
                    VALUES (
                            :A_Title,:A_Alias,
                            :A_CreatedDate,:A_Description,
                            :A_Content,:A_Image,
                            :A_ImageStatus,:A_ModifiedDate,
                            :A_Status,:A_Order,
                            :A_Hits,:A_CategoryID,
                            :A_HighLight,:A_CreatedUserID,
                            :A_ModifiedUserID
                            );";
            $parameters = array(
                ':A_Title', ':A_Alias',
                ':A_CreatedDate', ':A_Description',
                ':A_Content', ':A_Image',
                ':A_ImageStatus', ':A_ModifiedDate',
                ':A_Status', ':A_Order',
                ':A_Hits', ':A_CategoryID',
                ':A_HighLight', ':A_CreatedUserID',
                ':A_ModifiedUserID'
            );
            $values = array(
                $this->getTitle(), $this->getAlias(),
                $this->getCreatedDate(), $this->getDescription(),
                $this->getContent(), $this->getImage(),
                $this->getImageStatus(), $this->getModifiedDate(),
                $this->getStatus(), $this->getMaxOrder($db),
                $this->getHits(), $this->getCategoryID(),
                $this->getHighLight(), $this->getCreatedUserID(),
                $this->getModifiedUserID()
            );
            $types = array(
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_INT, PDO::PARAM_STR,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_INT,
                PDO::PARAM_INT
            );
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // hàm xóa bài viết
    public function delete($db) {
        if ($db != null) {
            $sql = "DELETE
                    FROM `articles`
                    WHERE `A_ID` = :A_ID;";
            $parameters = array(':A_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // hàm cập nhật bài viết
    public function update($db) {
        if ($db != null) {
            $sql = "UPDATE `articles`
                    SET
                      `A_Title` = :A_Title,
                      `A_Alias` = :A_Alias,                      
                      `A_Description` = :A_Description,
                      `A_Content` = :A_Content,
                      `A_Image` = :A_Image,
                      `A_ImageStatus` = :A_ImageStatus,
                      `A_ModifiedDate` = :A_ModifiedDate,
                      `A_Status` = :A_Status,                                            
                      `A_CategoryID` = :A_CategoryID,
                      `A_HighLight` = :A_HighLight,                      
                      `A_ModifiedUserID` = :A_ModifiedUserID
                    WHERE `A_ID` = :A_ID;";

            $parameters = array(
                ':A_Title', ':A_Alias',
                ':A_Description',
                ':A_Content', ':A_Image',
                ':A_ImageStatus', ':A_ModifiedDate',
                ':A_Status',
                ':A_CategoryID',
                ':A_HighLight',
                ':A_ModifiedUserID', ':A_ID'
            );
            $values = array(
                $this->getTitle(), $this->getAlias(),
                $this->getDescription(),
                $this->getContent(), $this->getImage(),
                $this->getImageStatus(), $this->getModifiedDate(),
                $this->getStatus(),
                $this->getCategoryID(),
                $this->getHighLight(),
                $this->getModifiedUserID(), $this->getID()
            );
            $types = array(
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_STR,
                PDO::PARAM_STR, PDO::PARAM_STR,
                PDO::PARAM_INT, PDO::PARAM_STR,
                PDO::PARAM_INT,
                PDO::PARAM_INT,
                PDO::PARAM_INT,
                PDO::PARAM_INT, PDO::PARAM_INT
            );


            return $db->executeNonQuery($sql, $parameters, $values, $types);
        }
    }

    // tải chi tiết 1 bài viết bởi mã bài viết
    public function getByID($db = null) {
        if ($db != null) {
            $sql = "SELECT
                        `A_ID`,
                        `A_Title`,
                        `A_Alias`,                       
                         Date_Format(`A_CreatedDate`, '%d/%m/%Y') AS `A_CreatedDate`,                          
                         `A_Description`,
                         `A_Content`,
                         `A_Image`,
                         `A_ImageStatus`,
                         Date_Format(`A_ModifiedDate`, '%d/%m/%Y') AS `A_ModifiedDate`,                            
                        `A_Status`,
                        `A_Order`,
                        `A_Hits`,
                        `A_CategoryID`,
                        `A_HighLight`,
                        `A_CreatedUserID`,
                        `A_ModifiedUserID`
                      FROM `articles`
                      WHERE A_ID = :A_ID";

            $parameters = array(':A_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                $row = $result->fetch();

                $this->setID($row->A_ID);
                $this->setTitle($row->A_Title);
                $this->setAlias($row->A_Alias);
                $this->setCreatedDate($row->A_CreatedDate);
                $this->setDescription($row->A_Description);
                $this->setContent($row->A_Content);
                $this->setImage($row->A_Image);
                $this->setImageStatus($row->A_ImageStatus);
                $this->setModifiedDate($row->A_ModifiedDate);
                $this->setStatus($row->A_Status);
                $this->setOrder($row->A_Order);
                $this->setHits($row->A_Hits);
                $this->setCategoryID($row->A_CategoryID);
                $this->setHighLight($row->A_HighLight);
                $this->setCreatedUserID($row->A_CreatedUserID);
                $this->setModifiedUserID($row->A_ModifiedUserID);


                // created user
                $createdUser = new Users();
                $createdUser->setID($this->getCreatedUserID());
                $createdUser->getByID($db);
                $this->setCreatedUser($createdUser);


                // modified user
                $modifiedUser = new Users();
                $modifiedUser->setID($this->getModifiedUserID());
                $modifiedUser->getByID($db);
                $this->setModifiedUser($modifiedUser);


                // category 
                $category = new Categories();
                $category->setID($this->getCategoryID());
                $category = $category->getByID($db);
                $this->setCategory($category);

                return $this;
            } else {
                return null;
            }
        }
    }

    // tải chi tiết 1 bài viết bởi mã bài viết
    public function getByAlias($db) {
        if ($db != null) {
            $sql = "SELECT
                        `A_ID`,
                        `A_Title`,
                        `A_Alias`,                       
                         Date_Format(`A_CreatedDate`, '%d/%m/%Y') AS `A_CreatedDate`,                          
                         `A_Description`,
                         `A_Content`,
                         `A_Image`,
                         `A_ImageStatus`,
                         Date_Format(`A_ModifiedDate`, '%d/%m/%Y') AS `A_ModifiedDate`,                            
                        `A_Status`,
                        `A_Order`,
                        `A_Hits`,
                        `A_CategoryID`,
                        `A_HighLight`,
                        `A_CreatedUserID`,
                        `A_ModifiedUserID`
                      FROM `articles`
                      WHERE A_Alias = :A_Alias";

            $parameters = array(':A_Alias');
            $values = array($this->getAlias());
            $types = array(PDO::PARAM_STR);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                $row = $result->fetch();

                $article = new Articles();
                $article->setID($row->A_ID);
                $article->setTitle($row->A_Title);
                $article->setAlias($row->A_Alias);
                $article->setCreatedDate($row->A_CreatedDate);
                $article->setDescription($row->A_Description);
                $article->setContent($row->A_Content);
                $article->setImage($row->A_Image);
                $article->setImageStatus($row->A_ImageStatus);
                $article->setModifiedDate($row->A_ModifiedDate);
                $article->setStatus($row->A_Status);
                $article->setOrder($row->A_Order);
                $article->setHits($row->A_Hits);
                $article->setCategoryID($row->A_CategoryID);
                $article->setHighLight($row->A_HighLight);
                $article->setCreatedUserID($row->A_CreatedUserID);
                $article->setModifiedUserID($row->A_ModifiedUserID);


                // created user
                $createdUser = new Users();
                $createdUser->setID($article->getCreatedUserID());
                $createdUser->getByID($db);
                $article->setCreatedUser($createdUser);


                // modified user
                $modifiedUser = new Users();
                $modifiedUser->setID($article->getModifiedUserID());
                $modifiedUser->getByID($db);
                $article->setModifiedUser($modifiedUser);

                // category 
                $category = new Categories();
                $category->setID($article->getCategoryID());
                $category = $category->getByID($db);
                $article->setCategory($category);

                return $article;
            } else {
                return null;
            }
        }
    }

    // hàm lấy thứ tự cao nhất
    public function getMaxOrder($db) {
        if ($db != null) {
            $sql = "SELECT MAX(A_Order) + 1 AS MaxOrder FROM articles";
            $parameters = null;
            $values = null;
            $types = null;
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            $row = $result->fetch();
            if ($row->MaxOrder != null) {
                return $row->MaxOrder;
            } else {
                return 1;
            }
        }
    }

    // ham su ly di chuyen mau tin xuong phia duoi cua news
    public function orderUp($db) {
        if ($db != null) {
            // câu lệnh tải currentOrder, nextOrder, nextArticleID
            $sql = "SELECT (SELECT `A_Order` 
                    from Articles 
                    WHERE A_ID = :A_ID) As `currenOrder`,               
                
                    (SELECT `A_Order` from Articles WHERE `A_Order` > 
                    (SELECT `A_Order` from Articles WHERE A_ID = :A_ID) 
                    Order by `A_Order` LIMIT 1) As `nextOrder`,

                    (SELECT A_ID from Articles WHERE `A_Order` = 
                    (SELECT `A_Order` from Articles WHERE `A_Order` > 
                    (SELECT `A_Order` from Articles WHERE A_ID = :A_ID) 
                    Order by `A_Order` LIMIT 1)) As `nextArticleID`";
            $parameters = array(':A_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);



            if ($result != null) {
                $row = $result->fetch();
//                echo $row->currenOrder . '<br/>';
//                echo $row->nextOrder . '<br/>';
//                echo $row->nextArticleID . '<br/>';
                // cập nhật order của previous article bằng với currentOrder
                $sqlNextArticle = "update Articles set `A_Order` = :A_Order where `A_ID` = :A_ID";
                $parameters2 = array(':A_Order', ':A_ID');
                $values2 = array($row->currenOrder, $row->nextArticleID);
                $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlNextArticle, $parameters2, $values2, $types2);

                // cập nhật order của current article bằng với previous order
                $sqlCurrentArticle = "update Articles set `A_Order` = :A_Order where `A_ID` = :A_ID";
                $parameters3 = array(':A_Order', ':A_ID');
                $values3 = array($row->nextOrder, $this->getID());
                $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlCurrentArticle, $parameters3, $values3, $types3);
            }
        }
    }

    function orderDown($db) {
        if ($db != null) {
            // câu lệnh tải currentOrder, previousOrder, previousArticleID
            $sql = "SELECT (SELECT `A_Order` from Articles 
                WHERE A_ID = :A_ID) As `currenOrder`, 
                    
		(SELECT `A_Order` from Articles WHERE `A_Order` < 
		(SELECT `A_Order` from Articles WHERE A_ID = :A_ID) 
		Order by `A_Order` desc LIMIT 1) As `previousOrder`,
                
		(SELECT A_ID from Articles WHERE `A_Order` = 
		(SELECT `A_Order` from Articles WHERE `A_Order` < 
		(SELECT `A_Order` from Articles WHERE A_ID = :A_ID) 
		Order by `A_Order` desc LIMIT 1)) As `previousArticleID`";

            $parameters = array(':A_ID');
            $values = array($this->getID());
            $types = array(PDO::PARAM_INT);

            $result = $db->executeQuery($sql, $parameters, $values, $types);

            if ($result != null) {

                $row = $result->fetch();

                // cập nhật order của previous article bằng với currentOrder
                $sqlPreviousArticle = "update Articles set `A_Order` = :A_Order where `A_ID` = :A_ID";
                $parameters2 = array(':A_Order', ':A_ID');
                $values2 = array($row->currenOrder, $row->previousArticleID);
                $types2 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlPreviousArticle, $parameters2, $values2, $types2);

                // cập nhật order của current article bằng với previous order
                $sqlCurrentArticle = "update Articles set `A_Order` = :A_Order where `A_ID` = :A_ID";
                $parameters3 = array(':A_Order', ':A_ID');
                $values3 = array($row->previousOrder, $this->getID());
                $types3 = array(PDO::PARAM_INT, PDO::PARAM_INT);
                $db->executeNonQuery($sqlCurrentArticle, $parameters3, $values3, $types3);
            }
        }
    }

    /**
     * get article list have the same category but have smaller order
     * @param type $db : connection
     * @return \Articles|null: array of article
     * 
     */
    public function getListOfTheSameCategory($db = null) {

        if ($db != null) {
            $sql = "SELECT
                          `A_ID`,
                          `A_Title`,
                          `A_Alias`,
                          Date_Format(`A_CreatedDate`, '%d/%m/%Y') AS `A_CreatedDate`,                          
                          `A_Description`,
                          `A_Content`,
                          `A_Image`,
                          `A_ImageStatus`,
                          Date_Format(`A_ModifiedDate`, '%d/%m/%Y') AS `A_ModifiedDate`,                          
                          `A_Status`,
                          `A_Order`,
                          `A_Hits`,
                          `A_CategoryID`,
                          `A_HighLight`,
                          `A_CreatedUserID`,
                          `A_ModifiedUserID`
                        FROM `articles`   
                        WHERE A_Order < :A_Order
                        AND A_CategoryID = :A_CategoryID
                        ORDER BY A_Order DESC
                        LIMIT 10";

            $parameters = array(':A_Order', ':A_CategoryID');
            $values = array($this->getOrder(), $this->getCategoryID());
            $types = array(PDO::PARAM_INT, PDO::PARAM_INT);
            $result = $db->executeQuery($sql, $parameters, $values, $types);

            // nếu có kết quả
            if ($result != null) {

                // mảng danh sách trả về
                $rows = array();

                // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                while ($row = $result->fetch()) {

                    $article = new Articles();
                    $article->setID($row->A_ID);
                    $article->setTitle($row->A_Title);
                    $article->setAlias($row->A_Alias);
                    $article->setCreatedDate($row->A_CreatedDate);
                    $article->setDescription($row->A_Description);
                    $article->setContent($row->A_Content);
                    $article->setImage($row->A_Image);
                    $article->setImageStatus($row->A_ImageStatus);
                    $article->setModifiedDate($row->A_ModifiedDate);
                    $article->setStatus($row->A_Status);
                    $article->setOrder($row->A_Order);
                    $article->setHits($row->A_Hits);
                    $article->setCategoryID($row->A_CategoryID);
                    $article->setHighLight($row->A_HighLight);

                    $article->setCreatedUserID($row->A_CreatedUserID);
                    $article->setModifiedUserID($row->A_ModifiedUserID);


                    // created user
                    $createdUser = new Users();
                    $createdUser->setID($article->getCreatedUserID());
                    $createdUser->getByID($db);
                    $article->setCreatedUser($createdUser);


                    // modified user
                    $modifiedUser = new Users();
                    $modifiedUser->setID($article->getModifiedUserID());
                    $modifiedUser->getByID($db);
                    $article->setModifiedUser($modifiedUser);


                    // category 
                    $category = new Categories();
                    $category->setID($article->getCategoryID());
                    $category = $category->getByID($db);
                    $article->setCategory($category);


                    $rows[] = $article;
                }
                return $rows;
            } else {
                return null;
            }
        }
    }

    // tải danh sách bài viết theo mã chủ đề
    public function search($db, $textSearch) {
        if ($db != null) {

            if ($textSearch != '') {

                $title = trim($textSearch, '"');

                $sql = "SELECT
                          `A_ID`,
                          `A_Title`,
                          `A_Alias`,
                          Date_Format(`A_CreatedDate`, '%d/%m/%Y') AS `A_CreatedDate`,                          
                          `A_Description`,
                          `A_Content`,
                          `A_Image`,
                          `A_ImageStatus`,
                          Date_Format(`A_ModifiedDate`, '%d/%m/%Y') AS `A_ModifiedDate`,                          
                          `A_Status`,
                          `A_Order`,
                          `A_Hits`,
                          `A_CategoryID`,
                          `A_HighLight`,
                          `A_CreatedUserID`,
                          `A_ModifiedUserID`
                        FROM `articles`
                        WHERE A_Title LIKE :TextSearch OR (";

                $parameters = array(':TextSearch');
                $values = array("%$title%");
                $types = array(PDO::PARAM_STR);




//                echo strlen($textSearch);
                // chạy hàm tách câu thành mảng ký tự riêng biệt
                $terms = Utility::search_split_terms($textSearch);

//                echo '<pre>';
//                var_dump($terms);
//                echo '</pre>';
//                echo $terms[0];
                // make sure each term matches on word boundaries
                // VD: 'foo' sẽ cho ra kết quả là 'a foo a' chứ không phải là 'a food a'
                $terms_db = Utility::search_db_escape_terms($terms);

//                echo $terms_db[0];

                $parts = array();
                foreach ($terms_db as $term_db) {
                    $parts[] = " A_Content RLIKE '$term_db'";
                }
                $parts = implode(' OR ', $parts);


                $sql .= $parts . ')';


                $result = $db->executeQuery($sql, $parameters, $values, $types);

                // nếu có kết quả
                if ($result != null) {

                    // mảng danh sách trả về
                    $rows = array();


                    // lấy mỗi dòng dữ liệu đưa vào mảng danh sách trả về
                    while ($row = $result->fetch()) {

                        // highlight mô tả bài viết và tiêu đề
                        $description = $row->A_Description;

                        $title = $row->A_Title;

//                        echo count($terms);

                        foreach ($terms as $term) {
//                            echo $term;
                            $highlight = new HighLight();
                            $description = $highlight->toHighlight($description, $term);
                            $title = $highlight->toHighlight($title, $term);
                        }


                        $article = new Articles();
                        $article->setTitle($title);
                        $article->setDescription($description);
                        $article->setID($row->A_ID);

                        $article->setAlias($row->A_Alias);
                        $article->setCreatedDate($row->A_CreatedDate);
                        $article->setContent($row->A_Content);
                        $article->setImage($row->A_Image);
                        $article->setImageStatus($row->A_ImageStatus);
                        $article->setModifiedDate($row->A_ModifiedDate);
                        $article->setStatus($row->A_Status);
                        $article->setOrder($row->A_Order);
                        $article->setHits($row->A_Hits);
                        $article->setCategoryID($row->A_CategoryID);
                        $article->setHighLight($row->A_HighLight);

                        $article->setCreatedUserID($row->A_CreatedUserID);
                        $article->setModifiedUserID($row->A_ModifiedUserID);


                        // created user
                        $createdUser = new Users();
                        $createdUser->setID($article->getCreatedUserID());
                        $createdUser->getByID($db);
                        $article->setCreatedUser($createdUser);


                        // modified user
                        $modifiedUser = new Users();
                        $modifiedUser->setID($article->getModifiedUserID());
                        $modifiedUser->getByID($db);
                        $article->setModifiedUser($modifiedUser);





                        $rows[] = $article;
                    }
                    return $rows;
                } else {
                    return null;
                }
            }
        }
    }

}

?>
